package com.apes.fico.finance.respository;

import com.apes.fico.finance.model.gl.GLVoucher;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface GLVoucherRepository extends CustomRepository<GLVoucher, String> {
    @Query(value = "SELECT A.* FROM GL_VOUCHER A WHERE A.ACC_VOUCHER_TYPE_ID = '000081' AND A.PERIOD = :period AND A.COMPANY_ID = :company",nativeQuery = true)
    List<GLVoucher> findAllByPeriodAndCompany_Id(@Param("period") String period,@Param("company") String company);

    GLVoucher findByPeriodAndCompany_IdAndVoucherTemplate_Description(String period, String company, String description);

    GLVoucher findByPeriodAndCompany_IdAndVoucherTemplate_VoucherTemplateGroup_Id(String period, String company, String group);

    @Query(value = "SELECT COUNT(*) FROM GL_VOUCHER A ,GL_VOUCHER_ITEM B WHERE A.ID = B.VOUCHER_ID AND A.COMPANY_ID = :companyId AND A.YEAR_MONTH = :period AND B.ACC_OPERATING_CHART_ID = :operatingChart",nativeQuery = true)
    int findByOperatingChart(@Param("companyId") String companyId, @Param("period") int period,@Param("operatingChart") String OperatingChart);

    List<GLVoucher> findByCompany_IdAndPeriod(String id, String period);

    @Query(value = "SELECT COUNT(*) FROM GL_VOUCHER A WHERE A.COMPANY_ID = :id AND A.PERIOD = :period AND A.STATE = 'sent'",nativeQuery = true)
    int findByCompanyAndPeriod(@Param("id") String id,@Param("period") String period);

    GLVoucher findFirstByPeriodAndCompany_idAndVoucherTemplate_IdAndStateOrderById(String period, String companyId, String voucherTemplateId, String state);

    @Query(value = "select a.company_id, a.year_month, a.id\n" +
            "  from gl_voucher a\n" +
            " where a.period = :period\n" +
            "   and a.company_id = :companyId\n" +
            "   and a.voucher_template_id = :voucherTemplateId\n" +
            "   and a.state = :state\n" +
            "   and a.id > :id\n" +
            " order by a.id",
            countQuery = "select count(*)\n" +
                    "  from gl_voucher a\n" +
                    " where a.period = :period\n" +
                    "   and a.company_id = :companyId\n" +
                    "   and a.voucher_template_id = :voucherTemplateId\n" +
                    "   and a.state = :state\n" +
                    "   and a.id > :id", nativeQuery = true)
    Page<Map> findNoApproveVoucher(@Param("period") String period, @Param("companyId") String companyId,
                                   @Param("voucherTemplateId") String voucherTemplateId, @Param("state") String state,
                                   @Param("id") String id, Pageable pageable);

    @Query(value = "SELECT T.* FROM GL_VOUCHER T where T.period = :period and T.company_id = :company" +
            " and T.VOUCHER_TEMPLATE_ID = :voucherTemplate and T.state = :state and t.post_date >= :start and t.post_date <= :end",nativeQuery = true)
    List<GLVoucher> findNoApproveVoucherByDate(@Param("period") String period, @Param("company") String company,
                                               @Param("voucherTemplate") String voucherTemplate, @Param("state") String state, @Param("start")Date start , @Param("end")Date end);

    @Query("SELECT A FROM GLVoucher A WHERE A.businessId = :businessId")
    List<GLVoucher> findAllByBusinessId(@Param("businessId") String businessId);
}
